﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Gacn.ControleAlunos.Library.Entities;
using System.Data.SqlClient;
using System.Globalization;

namespace Gacn.ControleAlunos.Library.DataAccess
{
    public class PessoaDataAccess
    {
        #region Get Methods
        public static DataTable GetAllPessoaDetalhadaEntity()
        {
            SqlConnection connection = null;
            SqlCommand command = null;
            DataTable dataTable = null;


            connection = Util.GetConnection();
            connection.Open();

            command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM [tbPessoa] WHERE flgAtivo = 'true'";
            command.CommandType = CommandType.Text;

            dataTable = new DataTable();
            dataTable.Locale = CultureInfo.InvariantCulture;
            new SqlDataAdapter(command).Fill(dataTable);

            return dataTable;
        }
        #endregion

        #region Insert Methods
        public static DataTable CadastraPessoa(PessoaDetalhadaEntity pessoa)
        {

            SqlConnection connection = null;
            SqlCommand command = null;
            DataTable dataTable = null;


            connection = Util.GetConnection();
            connection.Open();

            command = connection.CreateCommand();
            command.CommandText = "uspCadastraPessoa";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add("Email", SqlDbType.VarChar, 50).Value = pessoa.Email;
            command.Parameters.Add("DtNasc", SqlDbType.DateTime).Value = pessoa.Nascimento;
            command.Parameters.Add("NomePessoa", SqlDbType.VarChar, 160).Value = pessoa.NomePessoa;
            command.Parameters.Add("nrRoupaBaixo", SqlDbType.Char, 2).Value = pessoa.RoupaBaixo;
            command.Parameters.Add("nrRoupaCima", SqlDbType.Char, 2).Value = pessoa.RoupaCima;
            command.Parameters.Add("nrSapato", SqlDbType.Char, 2).Value = pessoa.NumeroSapato;
            command.Parameters.Add("obs", SqlDbType.VarChar, 2000).Value = pessoa.Observacao;
            command.Parameters.Add("nrRA", SqlDbType.Decimal).Value = pessoa.RA;
            command.Parameters.Add("aluSerie", SqlDbType.Char).Value = pessoa.Serie;
            command.Parameters.Add("Sexo", SqlDbType.Char).Value = pessoa.Sexo;
            command.Parameters.Add("nrFone", SqlDbType.Decimal).Value = pessoa.Telefone;
            command.Parameters.Add("nrFone2", SqlDbType.Decimal).Value = pessoa.Telefone2;
            command.Parameters.Add("idTipoPessoa", SqlDbType.Int).Value = pessoa.TipoPessoaId;
            command.Parameters.Add("idInsertUser", SqlDbType.Int).Value = 2;

            command.Parameters.Add("SUCESSO", SqlDbType.Bit).Direction = ParameterDirection.Output;
            command.Parameters.Add("ProcComment", SqlDbType.VarChar, 1000).Direction = ParameterDirection.Output;
            command.Parameters.Add("idPessoa", SqlDbType.Int).Direction = ParameterDirection.Output;

            dataTable = new DataTable();
            dataTable.Locale = CultureInfo.InvariantCulture;
            new SqlDataAdapter(command).Fill(dataTable);

            if (!(bool)command.Parameters["SUCESSO"].Value)
            {
                throw new Exception(string.Concat("Ocorreu um erro ao executar a procedure. \r\n", command.Parameters["ProcComment"].Value.ToString()));
            }

            return dataTable;
        }
        #endregion

        #region Update Methods
        public static DataTable UpdatePessoa(PessoaDetalhadaEntity pessoa)
        {
            SqlConnection connection = null;
            SqlCommand command = null;
            DataTable dataTable = null;


            connection = Util.GetConnection();
            connection.Open();

            command = connection.CreateCommand();
            command.CommandText = "uspUpdatePessoa";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add("idPessoa", SqlDbType.Int).Value = pessoa.ID;

            command.Parameters.Add("Email", SqlDbType.VarChar, 50).Value = pessoa.Email;
            command.Parameters.Add("DtNasc", SqlDbType.DateTime).Value = pessoa.Nascimento;
            command.Parameters.Add("NomePessoa", SqlDbType.VarChar, 160).Value = pessoa.NomePessoa;
            command.Parameters.Add("nrRoupaBaixo", SqlDbType.Char, 2).Value = pessoa.RoupaBaixo;
            command.Parameters.Add("nrRoupaCima", SqlDbType.Char, 2).Value = pessoa.RoupaCima;
            command.Parameters.Add("nrSapato", SqlDbType.Char, 2).Value = pessoa.NumeroSapato;
            command.Parameters.Add("obs", SqlDbType.VarChar, 2000).Value = pessoa.Observacao;
            command.Parameters.Add("nrRA", SqlDbType.Decimal).Value = pessoa.RA;
            command.Parameters.Add("aluSerie", SqlDbType.Char).Value = pessoa.Serie;
            command.Parameters.Add("Sexo", SqlDbType.Char).Value = pessoa.Sexo;
            command.Parameters.Add("nrFone", SqlDbType.Decimal).Value = pessoa.Telefone;
            command.Parameters.Add("nrFone2", SqlDbType.Decimal).Value = pessoa.Telefone2;
            command.Parameters.Add("idTipoPessoa", SqlDbType.Int).Value = pessoa.TipoPessoaId;
            command.Parameters.Add("idInsertUser", SqlDbType.Int).Value = 2;

            command.Parameters.Add("SUCESSO", SqlDbType.Bit).Direction = ParameterDirection.Output;
            command.Parameters.Add("ProcComment", SqlDbType.VarChar, 1000).Direction = ParameterDirection.Output;

            dataTable = new DataTable();
            dataTable.Locale = CultureInfo.InvariantCulture;
            new SqlDataAdapter(command).Fill(dataTable);

            if (!(bool)command.Parameters["SUCESSO"].Value)
            {
                throw new Exception(string.Concat("Ocorreu um erro ao executar a procedure. \r\n", command.Parameters["ProcComment"].Value.ToString()));
            }

            dataTable = new DataTable();
            dataTable.Locale = CultureInfo.InvariantCulture;
            new SqlDataAdapter(command).Fill(dataTable);

            return dataTable;
        }
        #endregion

    }
}
